use chrono::prelude::*;
pub mod grants;
pub mod table;
pub mod user;

use std::{collections::HashMap, fs::File, io::Read, path::PathBuf, process::exit};

use calamine::{open_workbook, Reader, Xlsx};

use oracle::pool::Pool;
use rust_xlsxwriter::{Color, Format, FormatAlign, FormatBorder, Note, Workbook};
use sqlite::State;

use crate::{OracleUserPrelude, UserPrelude};

const TABLE_EXT_HEADER: [&str; 15] = [
    "PK",
    "UK",
    "FK",
    "Triggers",
    "IOT",
    "Temp.",
    "Part.",
    "Interval Part.",
    "Compr.",
    "Ext.",
    "Limited-support Types",
    "Virtual Columns",
    "Hidden Columns",
    "Identity Columns",
    "Log Groups",
];

const TABLE_EXT_HEADER_NOTE: [&str; 15] = [
    "是否含有主键；无主键时为空",
    "唯一索引名列表，已排除主键；无唯一索引时为空",
    "外键名列表（不支持的对象）；无外键时为空",
    "触发器列表（不支持的对象）；无触发器时为空",
    "是否为索引组织表；非索引组织表时为空",
    "是否为临时表（不支持的对象）；非临时表时为空",
    "是否为分区表；非分区表时为空",
    "分区表是否包含间隔分区；不含间隔分区时为空",
    "是否为压缩表（不支持的对象）；非压缩表时为空",
    "是否为外部表（不支持的对象）；非外部表时为空",
    "是否含有有限支持或不支持的数据类型；不含有限支持且不支持数据类型时为空。格式为COLUMN_NAME:DATA_TYPE，有限支持列表为：{:1}，支持列表为：{:2}",
    "虚拟列列表（不支持的类型）；不含虚拟列时为空。格式为COLUMN_NAME",
    "隐藏列列表（不支持的类型）；不含隐藏列时为空。格式为COLUMN_NAME",
    "是否包含自增列（不支持的类型）；不含自增列时为空。",
    "表级别补充日志级别；不启用表级别补充日志时为空",
];

pub async fn probe(
    sqlite_conn: &sqlite::Connection,
    pool: &Pool,
    manifest_file: &PathBuf,
    data_type_support: &Vec<String>,
    data_type_support_limited: &Option<Vec<String>>,
    page_size: usize,
    report_file: &Option<PathBuf>,
    conn_user: &String,
    grant_manifest_file: &Option<PathBuf>,
    show_nullable_uk: bool,
) {
    // Schema, Table[]
    let mut tables: HashMap<String, Vec<String>> = HashMap::new();
    // 每次查询PAGE_SIZE行
    {
        let mut workbook: Xlsx<_> = open_workbook(manifest_file).expect(&format!(
            "--manifest-file={:?}:: Cannot open file",
            manifest_file
        ));
        for sheet_name in workbook.sheet_names() {
            let range = workbook.worksheet_range(&sheet_name).unwrap();
            let mut header_schema_index = 0;
            let mut header_table_index = 1;
            for (rindex, row) in range.rows().enumerate() {
                if rindex == 0 {
                    // 表头
                    for (cindex, item) in row.iter().enumerate() {
                        let s = item.to_string().to_uppercase();
                        if s.starts_with("SCHEMA") || s.ends_with("SCHEMA") {
                            header_schema_index = cindex;
                        } else if s.starts_with("TABLE") || s.ends_with("TABLE") {
                            header_table_index = cindex;
                        }
                    }
                    continue;
                }
                let s = row[header_schema_index].to_string().trim().to_string();
                let t = row[header_table_index].to_string().trim().to_string();
                tables.entry(s).or_insert_with(Vec::new).push(t);
            }
        }
    }

    // 查询数据库
    // 获取数据库版本
    let db_version = pool.get().unwrap().server_version().unwrap().0.major();
    if db_version < 11 {
        println!("Database Version: {db_version} incompatible");
        exit(1);
    }

    // 查询本地数据库，内置用户、内置表
    let mut build_in_users = Vec::new();
    {
        let query = "select username from oracle_build_in_user where version = ?";
        let mut statement = sqlite_conn.prepare(query).unwrap();
        statement
            .bind((1, format!("{}", db_version).as_str()))
            .unwrap();
        while let Ok(State::Row) = statement.next() {
            let user = statement.read::<String, _>("username").unwrap();
            build_in_users.push(user);
        }
    }
    let mut build_in_tables = Vec::new();
    {
        let query = "select owner, tablename from oracle_build_in_table where version = ?";
        let mut statement = sqlite_conn.prepare(query).unwrap();
        statement
            .bind((1, format!("{}", db_version).as_str()))
            .unwrap();
        while let Ok(State::Row) = statement.next() {
            let owner = statement.read::<String, _>("owner").unwrap();
            let tablename = statement.read::<String, _>("tablename").unwrap();
            build_in_tables.push(format!("{}.{}", owner, tablename));
        }
    }

    // 查询数据库用户
    let now: DateTime<Local> = Local::now();
    let users = user::query_users(pool, &tables, &build_in_users, page_size).await;
    let diff = Local::now() - now;
    println!(
        "{} users in set ({}.{} sec)",
        users.len(),
        diff.num_seconds(),
        diff.num_milliseconds()
    );

    let now: DateTime<Local> = Local::now();
    let (user_tables, table_count) = table::query_tables(
        &pool,
        &tables,
        &users,
        &build_in_tables,
        data_type_support,
        data_type_support_limited,
        page_size,
        show_nullable_uk,
    )
    .await;
    let diff = Local::now() - now;
    println!(
        "{} tables in set ({}.{} sec)",
        table_count,
        diff.num_seconds(),
        diff.num_milliseconds()
    );

    let mut query_oracle_user_prelude = None;
    let mut query_query_oracle_user_prelude = None;
    if let Some(grant_manifest_file) = grant_manifest_file {
        let mut grants_file = File::open(grant_manifest_file).unwrap();
        let mut contents = String::new();
        grants_file.read_to_string(&mut contents).unwrap();
        let mut user_prelude: UserPrelude = toml::from_str(&contents).unwrap();
        if user_prelude.user.is_none() {
            user_prelude.user = Some(conn_user.to_uppercase().clone());
        }
        query_oracle_user_prelude = Some(user_prelude.clone().oracle);

        // 权限
        query_query_oracle_user_prelude = Some(
            grants::query_grants(
                pool,
                &user_prelude.user.clone().unwrap(),
                &user_prelude.clone().oracle,
            )
            .await,
        );
    }

    let now: DateTime<Local> = Local::now();
    report(
        manifest_file,
        report_file,
        data_type_support,
        data_type_support_limited,
        &user_tables,
        &query_query_oracle_user_prelude,
        &query_oracle_user_prelude,
        show_nullable_uk
    )
    .await;
    let diff = Local::now() - now;
    println!(
        "{} tables to report ({}.{} sec)",
        table_count,
        diff.num_seconds(),
        diff.num_milliseconds()
    );
}

async fn report(
    manifest_file: &PathBuf,
    report_file: &Option<PathBuf>,
    data_type_support: &Vec<String>,
    data_type_support_limited: &Option<Vec<String>>,
    tables: &HashMap<String, Vec<table::Table>>,
    query_oracle_user_prelude: &Option<OracleUserPrelude>,
    oracle_user_prelude: &Option<OracleUserPrelude>,
    show_nullable_uk: bool,
) {
    //
    let mut workbook: Xlsx<_> = open_workbook(manifest_file).expect("Cannot open file");

    let mut comfy_table = comfy_table::Table::new();
    let mut report_workbook = Workbook::new();

    // let key_note = "主键或唯一索引，主键填写PRIMARYKEY（不区分大小写），唯一索引为索引名";
    if report_file.is_none() {
        let mut table_title = vec!["SCHEMA", "TABLE", "KEY"];
        for title in TABLE_EXT_HEADER {
            table_title.push(title);
        }
        comfy_table.set_header(table_title);
    } else {
        println!(
            "Writting Report to file {:?}...",
            report_file.clone().unwrap()
        );
    }

    let heading = Format::new()
        .set_bold()
        .set_font_color(Color::White)
        .set_background_color("#C01300")
        .set_border(FormatBorder::Thin);
    let warn_format = Format::new()
        .set_background_color(Color::Yellow)
        .set_border(FormatBorder::Thin)
        .set_align(FormatAlign::VerticalCenter);
    let author = env!("CARGO_PKG_NAME");
    let blank_format = Format::new()
        .set_border(FormatBorder::Thin)
        .set_align(rust_xlsxwriter::FormatAlign::VerticalCenter);
    let not_exists_note = Note::new("对象不存在").set_author(author);
    let unsupport_note = Note::new("对象不支持").set_author(author);
    let datatype_unsupport_note = Note::new("数据类型不支持").set_author(author);
    let datatype_support_limited_note = Note::new("数据类型有限支持").set_author(author);

    // Print
    for sheet_name in workbook.sheet_names() {
        let range = workbook.worksheet_range(&sheet_name).unwrap();
        let mut header_schema_index = 0;
        let mut header_table_index = 1;
        let mut header_table_key_index = None;
        let report_worksheet = report_workbook.add_worksheet();
        report_worksheet.set_name(sheet_name).unwrap();
        // report_worksheet.set_freeze_panes(0, 0).unwrap();

        let mut row_num: u32 = 0;

        for (rindex, row) in range.rows().enumerate() {
            let mut table_data: Vec<String> = Vec::new();

            if rindex == 0 {
                // 表头
                for (cindex, item) in row.iter().enumerate() {
                    let s = item.to_string().to_uppercase();
                    if s.starts_with("SCHEMA") || s.ends_with("SCHEMA") {
                        header_schema_index = cindex;
                    } else if s.starts_with("TABLE") || s.ends_with("TABLE") {
                        header_table_index = cindex;
                    } else if s.starts_with("KEY") || s.ends_with("KEY") {
                        header_table_key_index = Some(cindex);
                    }
                    // 生成表头
                    // 丢失这三个批注，无法从原文件中读取到
                    if report_file.is_none() {
                        continue;
                    }
                    table_data.push(s.clone());
                }

                // 输出excel表格模式
                if report_file.is_none() {
                    continue;
                }

                // 表头
                for (idx, title) in TABLE_EXT_HEADER.iter().enumerate() {
                    // 特殊处理
                    let note_text = if title.contains("Limited-support Types") {
                        let text =
                            if let Some(data_type_support_limited) = data_type_support_limited {
                                TABLE_EXT_HEADER_NOTE[idx]
                                    .replace("{:1}", &data_type_support_limited.join(","))
                            } else {
                                TABLE_EXT_HEADER_NOTE[idx].replace("{:1}", "<未配置>")
                            };
                        text.replace("{:2}", &data_type_support.join(","))
                    } else {
                        TABLE_EXT_HEADER_NOTE[idx].to_string()
                    };

                    table_data.push(title.to_string());
                    report_worksheet
                        .insert_note(
                            row_num,
                            (row.len() + idx) as u16,
                            &Note::new(note_text).set_author(author),
                        )
                        .unwrap();
                }

                report_worksheet
                    .write_row_with_format(row_num, 0, &table_data, &heading)
                    .unwrap();

                row_num += 1;

                continue;
            }

            // 数据
            let s = row[header_schema_index].to_string().trim().to_string();
            let t = row[header_table_index].to_string().trim().to_string();
            table_data = vec![s.clone(), t.clone()];
            if let Some(idx) = header_table_key_index {
                table_data.push(row[idx].to_string().trim().to_string());
            }

            if !tables.contains_key(&s) {
                // 用户不存在
                if report_file.is_none() {
                    // 打印到控制台
                    table_data[header_schema_index] =
                        format!("x {}", table_data[header_schema_index]);
                    comfy_table.add_row(table_data);
                } else {
                    report_worksheet
                        .insert_note(row_num, header_schema_index as u16, &not_exists_note)
                        .unwrap();
                    report_worksheet
                        .write_row(row_num, 0, table_data.clone())
                        .unwrap();
                    report_worksheet
                        .set_range_format(
                            row_num,
                            header_schema_index as u16,
                            row_num,
                            (table_data.len() + TABLE_EXT_HEADER.len() - 1) as u16,
                            &warn_format,
                        )
                        .unwrap();
                    row_num += 1;
                }

                continue;
            }

            let tables = match tables.get(&s) {
                Some(tables) => tables,
                None => {
                    continue;
                }
            };

            // 表是否存在
            let mut table_exists = false;
            for table in tables {
                if !t.is_empty() && table.table_name != t {
                    continue;
                }
                table_exists = true;

                // 新一行
                table_data = vec![s.clone(), table.table_name.clone()];

                let mut key_exists = false;
                if let Some(idx) = header_table_key_index {
                    let key = row[idx].to_string().trim().to_string();
                    table_data.push(key.clone());

                    if key.eq_ignore_ascii_case("primarykey") && table.pk.is_some() {
                        key_exists = true;
                    } else {
                        // 校验唯一索引
                        if let Some(uks) = table.uk.clone() {
                            for uk in uks.split(",") {
                                if key.eq_ignore_ascii_case(uk) {
                                    key_exists = true;
                                    break;
                                }
                            }
                        }
                    }
                }

                if table.table_name == "T_0" {
                    println!("xxxx");
                }

                let uk = if let Some(uks) = table.uk.clone() {
                    let mut new_uks = vec![];
                    for uk in uks.split(",") {

                        let mut is_null_uk = false;
                        if let Some(null_uks) = table.nullable_uk.clone() {
                            for null_uk in null_uks.split(",") {
                                if null_uk == uk {
                                    is_null_uk = true;
                                    break;
                                }
                            }
                        }

                        if !show_nullable_uk && is_null_uk {
                            continue;
                        }

                        if let Some(pk) = table.pk.clone() {
                            if uk != pk {
                                new_uks.push(uk);
                            }
                        } else {
                            new_uks.push(uk);
                        }

                        

                    }
                    new_uks.join(",")
                } else {
                    String::new()
                };

                let mut typ = match table.data_type_support_limited.clone() {
                    Some(t) => t.to_string(),
                    None => String::new(),
                };
                typ = match table.data_type_unsupport.clone() {
                    Some(t) => format!("{typ},{}", t.to_string()),
                    None => String::new(),
                };

                let mut ext_data = vec![
                    if table.pk.is_some() { "√" } else { "" }.to_string(),
                    uk,
                    match table.fk.clone() {
                        Some(fk) => fk.to_string(),
                        None => String::new(),
                    },
                    match table.triggers {
                        Some(_) => "√".to_string(),
                        None => String::new(),
                    },
                    if table.iot { "√" } else { "" }.to_string(),
                    if table.temporary { "√" } else { "" }.to_string(),
                    if table.partitioned { "√" } else { "" }.to_string(),
                    if table.partition_interval { "√" } else { "" }.to_string(),
                    if table.compression { "√" } else { "" }.to_string(),
                    if table.external { "√" } else { "" }.to_string(),
                    typ.trim_end_matches(",")
                        .trim_start_matches(",")
                        .to_string(),
                    match table.virtual_columns.clone() {
                        Some(vc) => vc.trim_end_matches(",").to_string(),
                        None => String::new(),
                    },
                    match table.hidden_columns.clone() {
                        Some(hc) => hc.trim_end_matches(",").to_string(),
                        None => String::new(),
                    },
                    if table.identity_column.is_some() { "√" } else { "" }.to_string(),
                    match table.supplemental_log.clone() {
                        Some(log) => log.to_string(),
                        None => String::new(),
                    },
                ];

                // 控制台打印
                if report_file.is_none() {
                    let mut data = vec![
                        format!("√ {}", s.clone()),
                        format!("√ {}", table.table_name.clone()),
                    ];

                    if let Some(key_index) = header_table_key_index {
                        if key_exists {
                            table_data[key_index] = format!("√ {}", table_data[key_index]);
                        }
                    }
                    data.append(&mut ext_data);
                    comfy_table.add_row(data);

                    continue;
                }

                table_data.append(&mut ext_data);
                // 写全部数据
                report_worksheet.write_row(row_num, 0, &table_data).unwrap();
                report_worksheet
                    .set_range_format(
                        row_num,
                        0,
                        row_num,
                        (table_data.len() - 1) as u16,
                        &blank_format,
                    )
                    .unwrap();
                // table_data.clear();
                let mut col = row.len() as u16;

                if let Some(idx) = header_table_key_index {
                    if !key_exists {
                        report_worksheet
                            .set_cell_format(row_num, idx as u16, &warn_format)
                            .unwrap();
                        report_worksheet
                            .insert_note(row_num, idx as u16, &not_exists_note)
                            .unwrap();
                    }
                }

                // if !table.pk {
                //     report_worksheet
                //         .set_cell_format(row_num, col, &warn_format)
                //         .unwrap();
                //     report_worksheet
                //         .insert_note(row_num, col, &not_exists_note)
                //         .unwrap();
                // }
                col += 1;
                // if table.uk.len() == 0 {
                //     report_worksheet
                //         .set_cell_format(row_num, col, &warn_format)
                //         .unwrap();
                //     report_worksheet
                //         .insert_note(row_num, col, &not_exists_note)
                //         .unwrap();
                // }
                col += 1;
                if table.fk.is_some() {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.triggers.is_some() {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.iot {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.temporary {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                col += 1;
                if table.partition_interval {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.compression {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.external {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                // col += 1;
                // if table.data_type_support_limited.is_some() {
                //     report_worksheet
                //         .set_cell_format(row_num, col, &warn_format)
                //         .unwrap();
                //     report_worksheet
                //         .insert_note(row_num, col, &datatype_support_limited_note)
                //         .unwrap();
                // }
                col += 1;
                if table.data_type_unsupport.is_some() || table.data_type_support_limited.is_some()
                {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();

                    let note;
                    if table.data_type_unsupport.is_some()
                        && table.data_type_support_limited.is_some()
                    {
                        note = Note::new(&format!(
                            "有限支持：{}\n不支持：{}",
                            table
                                .data_type_unsupport
                                .clone()
                                .unwrap()
                                .trim_end_matches(","),
                            table
                                .data_type_support_limited
                                .clone()
                                .unwrap()
                                .trim_end_matches(",")
                        ))
                        .set_author(author);
                    } else if table.data_type_unsupport.is_some() {
                        note = datatype_unsupport_note.clone();
                    } else {
                        note = datatype_support_limited_note.clone();
                    }
                    report_worksheet.insert_note(row_num, col, &note).unwrap();
                }
                col += 1;
                if table.virtual_columns.is_some() {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.hidden_columns.is_some() {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }
                col += 1;
                if table.identity_column.is_some() {
                    report_worksheet
                        .set_cell_format(row_num, col, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(row_num, col, &unsupport_note)
                        .unwrap();
                }

                row_num += 1;
            }

            // 表不存在
            if !table_exists {
                if report_file.is_none() {
                    // 打印到控制台
                    table_data[header_schema_index] =
                        format!("√ {}", table_data[header_schema_index]);
                    table_data[header_table_index] =
                        format!("x {}", table_data[header_table_index]);
                    comfy_table.add_row(table_data);
                    continue;
                }
                // 打印到表格
                report_worksheet.write_row(row_num, 0, &table_data).unwrap();
                report_worksheet
                    .insert_note(row_num, header_table_index as u16, &not_exists_note)
                    .unwrap();
                report_worksheet
                    .set_range_format(
                        row_num,
                        header_table_index as u16,
                        row_num,
                        (table_data.len() + TABLE_EXT_HEADER.len() - 1) as u16,
                        &warn_format,
                    )
                    .unwrap();
                report_worksheet
                    .set_cell_format(row_num, header_schema_index as u16, &blank_format)
                    .unwrap();

                row_num += 1;
            }

            // if report_file.is_some() {
            //     report_worksheet.autofit();
            // }
            // report_worksheet
            //     .set_column_width(header_table_index as u16, 30)
            //     .unwrap();
            // if let Some(idx) = header_table_key_index {
            //     report_worksheet.set_column_width(idx as u16, 10).unwrap();
            // }
        }
    }

    if let Some(report_file) = report_file {
        if query_oracle_user_prelude.is_some() && oracle_user_prelude.is_some() {
            let query_oracle_user_prelude = query_oracle_user_prelude.clone().unwrap();
            let oracle_user_prelude = oracle_user_prelude.clone().unwrap();

            let report_worksheet = report_workbook.add_worksheet();
            report_worksheet.set_name("用户权限与视图").unwrap();
            let odd_format = Format::new()
                .set_background_color(Color::Theme(4, 1))
                .set_border(FormatBorder::Thin)
                .set_align(FormatAlign::VerticalCenter);
            let even_format = Format::new()
                .set_background_color(Color::Theme(6, 1))
                .set_border(FormatBorder::Thin)
                .set_align(FormatAlign::VerticalCenter);
            report_worksheet.set_column_format(0, &odd_format).unwrap();
            report_worksheet.set_column_format(1, &even_format).unwrap();
            report_worksheet.set_column_format(2, &odd_format).unwrap();
            report_worksheet.set_column_format(3, &odd_format).unwrap();
            report_worksheet.set_column_format(4, &even_format).unwrap();

            let table_data = vec![
                "Expect System Privs",
                "Expect Role Privs",
                "Expect Table Privs",
                "",
                "Expect Dept. Views",
            ];
            let header_notes = vec![
                "权限列表【校验】",
                "角色列表【校验】",
                "表权限列表【校验】",
                "",
                "依赖视图【校验】",
            ];
            // 表头
            report_worksheet
                .write_row_with_format(0, 0, table_data.clone(), &heading)
                .unwrap();
            for (idx, note_text) in header_notes.iter().enumerate() {
                report_worksheet
                    .insert_note(
                        0,
                        idx as u16,
                        &Note::new(note_text.to_string()).set_author(author),
                    )
                    .unwrap();
            }
            report_worksheet
                .merge_range(0, 2, 0, 3, &table_data[2], &heading)
                .unwrap();

            // 权限
            report_worksheet
                .write_column(1, 0, oracle_user_prelude.sys_privs.clone())
                .unwrap();

            for (idx, sys_priv) in oracle_user_prelude.sys_privs.clone().iter().enumerate() {
                let mut exists = false;
                for sp in query_oracle_user_prelude.sys_privs.clone() {
                    if sp.eq_ignore_ascii_case(sys_priv) {
                        exists = true;
                        break;
                    }
                }
                if !exists {
                    report_worksheet
                        .set_cell_format((1 + idx) as u32, 0, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(
                            (1 + idx) as u32,
                            0,
                            &Note::new("权限未授予").set_author(author),
                        )
                        .unwrap();
                }
            }

            // 角色
            report_worksheet
                .write_column(1, 1, oracle_user_prelude.role_privs.clone())
                .unwrap();
            for (idx, role_priv) in oracle_user_prelude.role_privs.clone().iter().enumerate() {
                let mut exists = false;
                for rp in query_oracle_user_prelude.role_privs.clone() {
                    if rp.eq_ignore_ascii_case(role_priv) {
                        exists = true;
                        break;
                    }
                }
                if !exists {
                    report_worksheet
                        .set_cell_format((1 + idx) as u32, 1, &warn_format)
                        .unwrap();
                    report_worksheet
                        .insert_note(
                            (1 + idx) as u32,
                            1,
                            &Note::new("角色未授予").set_author(author),
                        )
                        .unwrap();
                }
            }

            // 表授权
            let mut row_index = 0;
            for (table, tab_privs) in oracle_user_prelude.tab_privs.clone() {
                if tab_privs.len() > 1 {
                    report_worksheet
                        .merge_range(
                            (row_index + 1) as u32,
                            2,
                            (row_index + tab_privs.len()) as u32,
                            2,
                            &table,
                            &odd_format,
                        )
                        .unwrap();
                } else {
                    report_worksheet
                        .write_with_format((row_index + 1) as u32, 2, &table, &odd_format)
                        .unwrap();
                }

                // 第3列，权限
                report_worksheet
                    .write_column((row_index + 1) as u32, 3, tab_privs.clone())
                    .unwrap();
                if !query_oracle_user_prelude.tab_privs.contains_key(&table) {
                    // 表不存在
                    report_worksheet
                        .insert_note(
                            (row_index + 1) as u32,
                            2,
                            &Note::new("表权限未授予").set_author(author),
                        )
                        .unwrap();

                    // report_worksheet
                    //     .set_cell_format((row_index + 1) as u32, 2, &warn_format)
                    //     .unwrap();
                    report_worksheet
                        .set_range_format(
                            (row_index + 1) as u32,
                            3,
                            (row_index + tab_privs.len()) as u32,
                            3,
                            &warn_format,
                        )
                        .unwrap();
                }

                // 权限不够
                for (idx, tab_priv) in tab_privs.clone().iter().enumerate() {
                    let mut exists = false;
                    if let Some(from_tab_privs) = query_oracle_user_prelude.tab_privs.get(&table) {
                        if from_tab_privs.contains(&tab_priv) {
                            exists = true;
                        }
                    }
                    if !exists {
                        report_worksheet
                            .insert_note(
                                (row_index + idx) as u32,
                                3,
                                &Note::new("表权限未授予").set_author(author),
                            )
                            .unwrap();
                        report_worksheet
                            .set_cell_format((row_index + idx) as u32, 3, &warn_format)
                            .unwrap();
                    }
                }

                row_index += tab_privs.len();
            }

            // User, Views
            let mut data = vec![];
            for (user, views) in oracle_user_prelude.dept_views.clone() {
                for view in views {
                    data.push(format!("{}.{}", user, view));
                }
            }
            report_worksheet
                .write_column_with_format(1, 4, data, &even_format)
                .unwrap();

            let mut row_index = 0;
            for (user, views) in oracle_user_prelude.dept_views.clone() {
                for view in views {
                    row_index += 1;
                    let mut exists = false;
                    let db_views = query_oracle_user_prelude.dept_views.get(&user);
                    if let Some(db_views) = db_views {
                        if db_views.contains(&view) {
                            exists = true;
                        }
                    }
                    if !exists {
                        report_worksheet
                            .insert_note(
                                row_index as u32,
                                4,
                                &Note::new("视图不存在").set_author(author),
                            )
                            .unwrap();
                        report_worksheet
                            .set_cell_format(row_index as u32, 4, &warn_format)
                            .unwrap();
                    }
                }
            }
        }

        // report_worksheet.write_column(1, 2, oracle_user_prelude.tab_privs.get("SYS").clone()).unwrap();
        // for (idx, role_priv) in oracle_user_prelude.role_privs.clone().iter().enumerate() {
        //     if !query_oracle_user_prelude.role_privs.contains(&role_priv) {
        //         report_worksheet.set_cell_format(1, idx as u16, &warn_format).unwrap();
        //         report_worksheet.insert_note(1, idx as u16, &unset_note).unwrap();
        //     }
        // }

        report_workbook.save(report_file).unwrap();
    } else {
        println!("{}", comfy_table.to_string());
    }

    // println!("Reported.")
}
